Create Dimensions and Fact Tables in OLAP Database and Populate the Dimension and Fact Tables
13
Download the SQL File attached to the
Run Script that is attached to Dimensions and Fact Tables in OLAP Database. Once ran, the tables will be made up of these columns.

After creating the Dimensions and Fact tables in OLAP Database
- Create a store procedure in the SQL Server to generate and load records into DimDate and Execute it for the date range from 01/01/2010 to 12/31/2020
Stored Procedure to populate DimDate, attached to the

- To populating each Dimension table, Create a store procedure in SQL Server by using Merge Statement to Insert and update that Dimension table
For Merge Statement Stored Procedures, SQL files attached to the

- Create a Package in SSIS and use Execute SQL Task for each Dimension table to execute relevant Stored Procedure and Populate that Dimension table accordingly

- Create another Package in SSIS to load Fact tables,
- Drag and drop a Data Flow Task to Load "FactNumberOfIncident"
- Inside Data Flow Task, use OLE DB Source and add OLE DB connection manager to ODS database and use "ShootingIncident" table
- Using 7 Lookup Transformation and configure them to populate IncidentKey, LocationKey, DistrictKey, RaceKey, HandlingUnitKey, WeaponKey, DateKey
- Use another Lookup for FactIncidentKey and configure it accordingly
- Use Derived Column Transformation to add RecordFlag and Effective Date Columns
- Connect "Lookup No Match Output" to Derived Column
- Use OLE DB Destination to insert new records from Derived Column output to Fact table
- Connect "Lookup Match Output" to an OLE DB command and configure it to update existing records

- Repeat the same steps to populate "FactSuspectRacialReport"

Execute packages to populate the Dimensions and Fact tables with the data


The Dimensions and Fact tables are populated with the data

INCREMENTAL DATA LOAD IN SSIS